﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for PartDBConnection
/// </summary>
public class PartDBConnection
{
    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataAdapter datatAdapter;
    OleDbDataReader dataReader;
    public String connString = "";

	public PartDBConnection()
    {
        conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;
	}

    public Part getPartByID(int partID)
    {
        connString = "SELECT * FROM Part WHERE Part_ID = @Part_ID";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Part_ID";
        computerIDParam.OleDbType = OleDbType.Numeric;
        computerIDParam.Size = 255;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = partID;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Part part = new Part();
        while (dataReader.Read())
        {
            part.part_ID = Int32.Parse(dataReader["Part_ID"].ToString());
            part.computer_ID = Int32.Parse(dataReader["Computer_ID"].ToString());
            part.install_Date = DateTime.Parse(dataReader["Install_Date"].ToString());
            part.part_Type = dataReader["Part_Type"].ToString();
            part.part_Serial = dataReader["Part_Serial"].ToString();
            part.part_Description = dataReader["Part_Description"].ToString();
        }

        closeObjects();

        return part;
    }

    public Part getUninstalledPartByID(int partID)
    {
        connString = "SELECT * FROM Part WHERE Part_ID = @Part_ID";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Part_ID";
        computerIDParam.OleDbType = OleDbType.Numeric;
        computerIDParam.Size = 255;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = partID;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Part part = new Part();
        while (dataReader.Read())
        {
            part.part_ID = Int32.Parse(dataReader["Part_ID"].ToString());
            //part.computer_ID = Int32.Parse(dataReader["Computer_ID"].ToString());
            part.install_Date = DateTime.Parse(dataReader["Install_Date"].ToString());
            part.part_Type = dataReader["Part_Type"].ToString();
            part.part_Serial = dataReader["Part_Serial"].ToString();
            part.part_Description = dataReader["Part_Description"].ToString();
        }

        closeObjects();

        return part;
    }

    public DataSet getPartDataSet()
    {
        DataSet ds = new DataSet();

        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Part WHERE Computer_ID IS NULL;", conn);
        da.Fill(ds, "PartsTable");

        closeObjects();

        return ds;
    }

    public void updateAll(Part part)
    {
        String connStringUpdate = "UPDATE Part SET Install_Date = '"+part.install_Date+"', Part_Serial '" + part.part_Serial + "', Part_Description = '" + part.part_Description + "', Part_Type = '" + part.part_Type + "' WHERE Part.Part_ID = " + part.part_ID + ";";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public void installUpdate(Part part)
    {
        String connStringUpdate = "UPDATE Part SET Computer_ID = " + part.computer_ID + " WHERE Part_ID = " + part.part_ID + ";";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public void delete(Part part)
    {
        connString = "DELETE FROM Part WHERE Part_ID = " + part.part_ID + ";";
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;

        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public void insert(Part part)
    {
        String connString = "INSERT INTO Part (Part_ID,Install_Date,Part_Type,Part_Serial,Part_Description) VALUES (" + part.part_ID + ",'" + part.install_Date + "','" + part.part_Type + "','" + part.part_Serial + "','" + part.part_Description + "');";

        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();

    }

    public int getNewPartNo()
    {
        connString = "SELECT max(Part_ID) AS [New_Part_Number] FROM Part;";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();
        OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        int partID = -1;
        while (dataReader.Read())
        {
            partID = Int32.Parse(dataReader["New_Part_Number"].ToString());
        }

        closeObjects();

        return (partID+1);
    }


    public void closeObjects()
    {
        if (conn.State.ToString().Equals("Open"))
        {
            conn.Close();
        }
        if (cmd.Connection.State.ToString().Equals("Open"))
        {
            cmd.Connection.Close();
        }
    }

}